package com.uyayo.framework.excelhelper.service.impl;

import com.uyayo.framework.excelhelper.ExcelDefaultFactory;
import com.uyayo.framework.excelhelper.ExcelFont;
import com.uyayo.framework.excelhelper.ExcelStyle;
import com.uyayo.framework.excelhelper.service.BaseExportExcelService;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;

import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

/**

 * 利用开源组件POI动态导出EXCEL文档


 * @author hfj

 * @version v1.0

 * @param <T> 应用泛型，代表任意一个符合javabean风格的类

 * 注意这里为了简单起见，boolean型的属性xxx的get器方式为getXxx(),而不是isXxx()

 * byte[]表jpg格式的图片数据

 */
public class BaseExportExcelServiceImpl<T> implements BaseExportExcelService<T> {

    @Override
    public void exportExcel(Workbook work,Collection<T> dataset, OutputStream out) {

        exportExcel(work,"导出EXCEL文档", null, dataset, out, "yyyy-MM-dd");

    }

    @Override
    public void exportExcel(Workbook work,String[] headers, Collection<T> dataset,OutputStream out) {

        exportExcel(work,"导出EXCEL文档", headers, dataset, out, "yyyy-MM-dd");

    }

    @Override
    public void exportExcel(Workbook work,String[] headers, Collection<T> dataset,OutputStream out, String pattern) {

        exportExcel(work,"导出EXCEL文档", headers, dataset, out, pattern);

    }

    @Override
    public void exportExcel(Workbook work,String title,String[] headers,String[] fieldNames, Collection<T> dataset,OutputStream out) {

        exportExcel(work,title, headers,fieldNames, dataset, out, "yyyy-MM-dd");

    }


    @Override
    public void exportExcel(Workbook work,String title, String[] headers,Collection<T> dataset, OutputStream out, String pattern) {

        Workbook workbook = getWorkbook(work,title, headers, dataset, pattern);

        try {

            workbook.write(out);

        } catch (IOException e) {

            e.printStackTrace();

        }

    }

    @Override
    public void exportExcel(Workbook work,String title,String[] headers,String[] fieldNames,Collection<T> dataset, OutputStream out, String pattern) {

        Workbook workbook = getWorkbook(work,title, headers, fieldNames, dataset, pattern);

        try {

            workbook.write(out);

        } catch (IOException e) {

            e.printStackTrace();

        }
    }


    public InputStream getExcelStream(Workbook work,String title,String[] headers,String[] fieldNames,Collection<T> dataset,String pattern) {

        Workbook workbook = getWorkbook(work,title, headers, fieldNames, dataset, pattern);

        InputStream is=null;

        try {
            File file = File.createTempFile(title,null);

            OutputStream os=new FileOutputStream(file);

            workbook.write(os);

            os.close();

            is=new FileInputStream(file);

        } catch (FileNotFoundException e) {

            e.printStackTrace();

        } catch (IOException e) {
            e.printStackTrace();
        }

        return is;//返回的是一个输入流

    }



    public InputStream getExcelStream(Workbook workbook,String title, List<Integer> sheetNum, List<String> sheetTitle, List<String[]> headers, List<String[]> fieldNames, List<List<T>> dataset, String pattern) {

        for (int i = 0; i < sheetNum.size(); i++) {
            this.exportExcel(workbook,sheetNum.get(i),sheetTitle.get(i),headers.get(i),fieldNames.get(i), dataset.get(i), pattern);
        }

        InputStream is=null;

        try {
            File file = File.createTempFile(title,null);

            OutputStream os=new FileOutputStream(file);

            workbook.write(os);

            os.close();

            is=new FileInputStream(file);

        } catch (FileNotFoundException e) {

            e.printStackTrace();

        } catch (IOException e) {
            e.printStackTrace();
        }

        return is;//返回的是一个输入流

    }

    @Override
    @SuppressWarnings({ "deprecation", "unchecked", "rawtypes" })
    public void exportExcel(Workbook workbook,int sheetNum,String sheetTitle,String[] headers,String[] fieldNames,Collection<T> dataset,String pattern){


        // 生成一个表格

        Sheet sheet = workbook.createSheet();
        workbook.setSheetName(sheetNum, sheetTitle);

        // 设置表格默认列宽度为15个字节

        sheet.setDefaultColumnWidth((short) 15);


        ExcelDefaultFactory excelDefaultFactory = new ExcelDefaultFactory();    //1.创建默认样式工厂

        ExcelStyle excelStyle = excelDefaultFactory.createStyle();              //2.创建样式对象
        ExcelFont excelFont = excelDefaultFactory.createFont();

        CellStyle headerStyle =  excelStyle.getHeadersStyle(workbook);      //获取表头样式

        Font headersFont = excelFont.getHeadersFont(workbook);              //获取表头字体

        // 把字体应用到当前的样式

        headerStyle.setFont(headersFont);

        CellStyle contentStyle =  excelStyle.getContentStyle(workbook);     //获取表内容样式

        Font contentFont = excelFont.getContentFont(workbook);              //获取表内容字体

        // 把字体应用到当前的样式

        contentStyle.setFont(contentFont);

        // 声明一个画图的顶级管理器

        Drawing patriarch = sheet.createDrawingPatriarch();

        // 定义注释的大小和位置,详见文档

        Comment comment = patriarch.createCellComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5));

        // 设置注释内容

        comment.setString(new HSSFRichTextString(""));

        // 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.

        comment.setAuthor("hfj");

        // 产生表格标题行

        Row row = sheet.createRow(0);

        if(headers==null || headers.length<=0){
            for (short i = 0; i < fieldNames.length; i++) {

                Cell cell = row.createCell(i);

                cell.setCellStyle(headerStyle);

                HSSFRichTextString text = new HSSFRichTextString(fieldNames[i]);

                cell.setCellValue(text);

            }
        }else{
            for (short i = 0; i < headers.length; i++) {

                Cell cell = row.createCell(i);

                cell.setCellStyle(headerStyle);

                HSSFRichTextString text = new HSSFRichTextString(headers[i]);

                cell.setCellValue(text);

            }
        }


        // 遍历集合数据，产生数据行
        Iterator<T> it = dataset.iterator();

        int index = 0;

        while (it.hasNext()) {

            index++;
            if(index==65536){
                break;
            }
            row = sheet.createRow(index);

            T t = (T) it.next();

            // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值

            Field[] fields = t.getClass().getDeclaredFields();

            for (int j = 0; j < fieldNames.length; j++) {
                for (int i = 0; i < fields.length; i++) {
                    Field field = fields[i];
                    String fieldName = field.getName();
                    if(fieldName!=null && fieldName.equals(fieldNames[j])){
                        Cell cell = row.createCell(j);

                        cell.setCellStyle(contentStyle);

                        String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);

                        try {

                            Class tCls = t.getClass();

                            Method method = tCls.getMethod(getMethodName,new Class[] {});

                            Object value = method.invoke(t, new Object[] {});

                            // 判断值的类型后进行强制类型转换

                            String textValue = null;

                            if(value == null){

                            } else if (value instanceof Integer) {

                                int intValue = (Integer) value;

                                cell.setCellValue(intValue);

                            } else if (value instanceof Float) {

                                float fValue = (Float) value;

                                cell.setCellValue(fValue);

                            } else if (value instanceof Double) {

                                double dValue = (Double) value;

                                cell.setCellValue(dValue);

                            } else if(value instanceof Boolean){

                                boolean booleanValue = (boolean) value;
                                cell.setCellValue(booleanValue);

                            }  else if (value instanceof Long) {

                                long longValue = (Long) value;

                                cell.setCellValue(longValue);

                            } else if (value instanceof Date) {

                                Date date = (Date) value;

                                SimpleDateFormat sdf = new SimpleDateFormat(pattern);

                                textValue = sdf.format(date);

                            } else if (value instanceof byte[]) {

                                // 有图片时，设置行高为60px;

                                row.setHeightInPoints(60);

                                // 设置图片所在列宽度为80px,注意这里单位的一个换算

                                sheet.setColumnWidth(j, (short) (35.7 * 80));

                                // sheet.autoSizeColumn(i);

                                byte[] bsValue = (byte[]) value;

                                HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0,1023, 255, (short) 6, index, (short) 6, index);

                                anchor.setAnchorType(ClientAnchor.AnchorType.byId(2));

                                patriarch.createPicture(anchor, workbook.addPicture(bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));

                            } else {
                                // 其它数据类型都当作字符串简单处理
                                textValue = value.toString();
                            }

                            // 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成

                            if (textValue != null) {

                                HSSFRichTextString richString = new HSSFRichTextString(textValue);

                                cell.setCellValue(richString);
                            }

                        } catch (SecurityException e) {

                            e.printStackTrace();

                        } catch (NoSuchMethodException e) {

                            e.printStackTrace();

                        } catch (IllegalArgumentException e) {

                            e.printStackTrace();

                        } catch (IllegalAccessException e) {

                            e.printStackTrace();

                        } catch (InvocationTargetException e) {

                            e.printStackTrace();

                        } finally {

                            // 清理资源

                        }
                    }
                }
            }
        }
    }


    @Override
    @SuppressWarnings({ "deprecation", "unchecked", "rawtypes" })
    public Workbook getWorkbook(Workbook workbook,String title,String[] headers,Collection<T> dataset,String pattern){
        // 声明一个工作薄

        // 生成一个表格

        Sheet sheet = workbook.createSheet(title);

        // 设置表格默认列宽度为15个字节

        sheet.setDefaultColumnWidth((short) 15);


        ExcelDefaultFactory excelDefaultFactory = new ExcelDefaultFactory();    //1.创建默认样式工厂

        ExcelStyle excelStyle = excelDefaultFactory.createStyle();              //2.创建样式对象
        ExcelFont excelFont = excelDefaultFactory.createFont();

        CellStyle headerStyle =  excelStyle.getHeadersStyle(workbook);      //获取表头样式

        Font headersFont = excelFont.getHeadersFont(workbook);              //获取表头字体

        // 把字体应用到当前的样式

        headerStyle.setFont(headersFont);

        CellStyle contentStyle =  excelStyle.getContentStyle(workbook);     //获取表内容样式

        Font contentFont = excelFont.getContentFont(workbook);              //获取表内容字体

        // 把字体应用到当前的样式

        contentStyle.setFont(contentFont);

        // 声明一个画图的顶级管理器

        Drawing patriarch = sheet.createDrawingPatriarch();

        // 定义注释的大小和位置,详见文档

        Comment comment = patriarch.createCellComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5));

        // 设置注释内容

        comment.setString(new HSSFRichTextString("excel！"));

        // 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.

        comment.setAuthor("hfj");

        // 产生表格标题行

        Row row = sheet.createRow(0);

        for (short i = 0; i < headers.length; i++) {

            Cell cell = row.createCell(i);

            cell.setCellStyle(headerStyle);

            HSSFRichTextString text = new HSSFRichTextString(headers[i]);

            cell.setCellValue(text);

        }

        // 遍历集合数据，产生数据行
        Iterator<T> it = dataset.iterator();

        int index = 0;

        while (it.hasNext()) {

            index++;
            if(index==65536){
                break;
            }
            row = sheet.createRow(index);

            T t = (T) it.next();

            // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值

            Field[] fields = t.getClass().getDeclaredFields();

            for (int i = 0; i < fields.length; i++) {
                Cell cell = row.createCell(i);

                cell.setCellStyle(contentStyle);

                Field field = fields[i];

                String fieldName = field.getName();

                String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);

                try {

                    Class tCls = t.getClass();

                    Method method = tCls.getMethod(getMethodName,new Class[] {});

                    Object value = method.invoke(t, new Object[] {});

                    // 判断值的类型后进行强制类型转换

                    String textValue = null;


                    if(value == null){

                    }else if (value instanceof Integer) {

                        int intValue = (Integer) value;

                        cell.setCellValue(intValue);

                    }else if (value instanceof Float) {

                        float fValue = (Float) value;

                        cell.setCellValue(fValue);

                    } else if (value instanceof Double) {

                        double dValue = (Double) value;

                        cell.setCellValue(dValue);

                    } else if (value instanceof Long) {

                        long longValue = (Long) value;

                        cell.setCellValue(longValue);

                    } else if(value instanceof Boolean){

                        boolean booleanValue = (boolean) value;
                        cell.setCellValue(booleanValue);

                    } else if (value instanceof Date) {

                        Date date = (Date) value;

                        SimpleDateFormat sdf = new SimpleDateFormat(pattern);

                        textValue = sdf.format(date);

                    } else if (value instanceof byte[]) {

                        // 有图片时，设置行高为60px;

                        row.setHeightInPoints(60);

                        // 设置图片所在列宽度为80px,注意这里单位的一个换算

                        sheet.setColumnWidth(i, (short) (35.7 * 80));

                        // sheet.autoSizeColumn(i);

                        byte[] bsValue = (byte[]) value;

                        HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0,1023, 255, (short) 6, index, (short) 6, index);

                        anchor.setAnchorType(ClientAnchor.AnchorType.byId(2));

                        patriarch.createPicture(anchor, workbook.addPicture(bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));

                    } else {
                        // 其它数据类型都当作字符串简单处理
                        textValue = value.toString();
                    }

                    // 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成

                    if (textValue != null) {

                        HSSFRichTextString richString = new HSSFRichTextString(textValue);

                        cell.setCellValue(richString);

                    }

                } catch (SecurityException e) {

                    e.printStackTrace();

                } catch (NoSuchMethodException e) {

                    e.printStackTrace();

                } catch (IllegalArgumentException e) {

                    e.printStackTrace();

                } catch (IllegalAccessException e) {

                    e.printStackTrace();

                } catch (InvocationTargetException e) {

                    e.printStackTrace();

                } finally {

                    // 清理资源

                }

            }

        }
        return workbook;
    }

    @Override
    @SuppressWarnings({ "deprecation", "unchecked", "rawtypes" })
    public Workbook getWorkbook(Workbook workbook,String title,String[] headers,String[] fieldNames,Collection<T> dataset,String pattern) {
        RichTextString richTextString = new HSSFRichTextString("");
        ClientAnchor clientAnchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5);

        // 生成一个表格
        Sheet sheet = workbook.createSheet(title);

        // 设置表格默认列宽度为15个字节

        sheet.setDefaultColumnWidth((short) 15);


        ExcelDefaultFactory excelDefaultFactory = new ExcelDefaultFactory();    //1.创建默认样式工厂

        ExcelStyle excelStyle = excelDefaultFactory.createStyle();              //2.创建样式对象
        ExcelFont excelFont = excelDefaultFactory.createFont();

        CellStyle headerStyle =  excelStyle.getHeadersStyle(workbook);      //获取表头样式

        Font headersFont = excelFont.getHeadersFont(workbook);              //获取表头字体

        // 把字体应用到当前的样式

        headerStyle.setFont(headersFont);

        CellStyle contentStyle =  excelStyle.getContentStyle(workbook);     //获取表内容样式

        Font contentFont = excelFont.getContentFont(workbook);              //获取表内容字体

        // 把字体应用到当前的样式

        contentStyle.setFont(contentFont);

        // 声明一个画图的顶级管理器

        Drawing patriarch = sheet.createDrawingPatriarch();

        // 定义注释的大小和位置,详见文档

        Comment comment = patriarch.createCellComment(clientAnchor);

        // 设置注释内容

        comment.setString(richTextString);

        // 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.

        comment.setAuthor("hfj");

        // 产生表格标题行

        Row row = sheet.createRow(0);

        for (short i = 0; i < headers.length; i++) {

            Cell cell = row.createCell(i);

            cell.setCellStyle(headerStyle);

            HSSFRichTextString text = new HSSFRichTextString(headers[i]);

            cell.setCellValue(text);

        }

        // 遍历集合数据，产生数据行
        Iterator<T> it = dataset.iterator();

        int index = 0;

        while (it.hasNext()) {

            index++;
            if(index==65536){
                break;
            }
            row = sheet.createRow(index);

            T t = (T) it.next();

            // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值

            Field[] fields = t.getClass().getDeclaredFields();

            for (int j = 0; j < fieldNames.length; j++) {
                for (int i = 0; i < fields.length; i++) {
                    Field field = fields[i];
                    String fieldName = field.getName();
                    if(fieldName!=null && fieldName.equals(fieldNames[j])){
                        Cell cell = row.createCell(j);

                        cell.setCellStyle(contentStyle);

                        String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);

                        try {

                            Class tCls = t.getClass();

                            Method method = tCls.getMethod(getMethodName,new Class[] {});

                            Object value = method.invoke(t, new Object[] {});

                            // 判断值的类型后进行强制类型转换

                            String textValue = null;

                            if(value == null){

                            }else if (value instanceof Integer) {

                                int intValue = (Integer) value;

                                cell.setCellValue(intValue);

                            }else if (value instanceof Float) {

                                float fValue = (Float) value;

                                cell.setCellValue(fValue);

                            } else if (value instanceof Double) {

                                double dValue = (Double) value;

                                cell.setCellValue(dValue);

                            } else if(value instanceof Boolean){

                                boolean booleanValue = (boolean) value;
                                cell.setCellValue(booleanValue);

                            }  else if (value instanceof Long) {

                                long longValue = (Long) value;

                                cell.setCellValue(longValue);

                            } else if (value instanceof Date) {

                                Date date = (Date) value;

                                SimpleDateFormat sdf = new SimpleDateFormat(pattern);

                                textValue = sdf.format(date);

                            } else if (value instanceof byte[]) {

                                // 有图片时，设置行高为60px;

                                row.setHeightInPoints(60);

                                // 设置图片所在列宽度为80px,注意这里单位的一个换算

                                sheet.setColumnWidth(j, (short) (35.7 * 80));

                                // sheet.autoSizeColumn(i);

                                byte[] bsValue = (byte[]) value;

                                HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0,1023, 255, (short) 6, index, (short) 6, index);

                                anchor.setAnchorType(ClientAnchor.AnchorType.byId(2));

                                patriarch.createPicture(anchor, workbook.addPicture(bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));

                            } else {
                                // 其它数据类型都当作字符串简单处理
                                textValue = value.toString();
                            }

                            // 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成

                            if (textValue != null) {

                                HSSFRichTextString richString = new HSSFRichTextString(textValue);

                                cell.setCellValue(richString);
                            }

                        } catch (SecurityException e) {

                            e.printStackTrace();

                        } catch (NoSuchMethodException e) {

                            e.printStackTrace();

                        } catch (IllegalArgumentException e) {

                            e.printStackTrace();

                        } catch (IllegalAccessException e) {

                            e.printStackTrace();

                        } catch (InvocationTargetException e) {

                            e.printStackTrace();

                        } finally {

                            // 清理资源

                        }
                    }
                }
            }
        }

        return workbook;

    }
}